{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "initial_id",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-10-13T10:43:35.396891700Z",
     "start_time": "2023-10-13T10:43:34.834320800Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1;32m  _____                _                                                  _                       _         \n",
      "|  __ \\              | |                   /\\                           | |                     (_)        \n",
      "| |  | |     __ _    | |_      __ _       /  \\       _ __       __ _    | |    _   _     ___     _     ___ \n",
      "| |  | |    / _` |   | __|    / _` |     / /\\ \\     | '_ \\     / _` |   | |   | | | |   / __|   | |   / __|\n",
      "| |__| |   | (_| |   | |_    | (_| |    / ____ \\    | | | |   | (_| |   | |   | |_| |   \\__ \\   | |   \\__ \\\n",
      "|_____/     \\__,_|    \\__|    \\__,_|   /_/    \\_\\   |_| |_|    \\__,_|   |_|    \\__, |   |___/   |_|   |___/\n",
      "                                                                                __/ |                      \n",
      "                                                                               |___/                       \n",
      "\t@From :  品管部\n",
      "\t@version :  1.1.0\n",
      "\t@PandasVersion: 2.1.0\n",
      "\t@NumpyVersion: 1.25.1\u001b[0m\n",
      "BOM清单\n",
      "          序号  层级     零件名称         零件编号      供应商  SZHBG-2023  SZHBG-2024  \\\n",
      "0          1   2      零件1  FOSFC-97058  供应商1994           8          10   \n",
      "1          2   3      零件2  JYBWK-27081   供应商166           2           8   \n",
      "2          3   1      零件3  IIVGK-51680   供应商174           0           5   \n",
      "3          4   2      零件4  BCGZK-18967   供应商289           9           4   \n",
      "4          5   3      零件5  EIZBA-09568  供应商1256           1           7   \n",
      "...      ...  ..      ...          ...      ...         ...         ...   \n",
      "19995  19996   2  零件19996  SARCL-96748   供应商673           6           3   \n",
      "19996  19997   2  零件19997  MFZWY-69428   供应商926           4           2   \n",
      "19997  19998   2  零件19998  PTACG-79360   供应商344           4          10   \n",
      "19998  19999   1  零件19999  YLKTM-42175  供应商1841           7           7   \n",
      "19999  20000   2  零件20000  DAYJQ-53618  供应商1673           3           3   \n",
      "\n",
      "       SZHBG-2025  SZHBG-2026  SZHBG-2027  ...  SZHBG-2031  SZHBG-2032  \\\n",
      "0              10          10           4  ...           5           1   \n",
      "1               5           0           0  ...           2           0   \n",
      "2               0           7           4  ...           1           7   \n",
      "3               1           1           1  ...           2           1   \n",
      "4               8           2          10  ...           9          10   \n",
      "...           ...         ...         ...  ...         ...         ...   \n",
      "19995           4           9           6  ...           2           5   \n",
      "19996           3           3           7  ...           6           7   \n",
      "19997           5          10           0  ...           4           7   \n",
      "19998          10           5           2  ...           2           5   \n",
      "19999           0           9           3  ...           2           3   \n",
      "\n",
      "       SZHBG-2033  SZHBG-2034  SZHBG-2035  SZHBG-2036  SZHBG-2037  SZHBG-2038  \\\n",
      "0               4          10           9           5           8           4   \n",
      "1               0           9           8          10           8           5   \n",
      "2               6           8           9           6           5           3   \n",
      "3               5           6           8           7           9           2   \n",
      "4               9           7          10           5           9           9   \n",
      "...           ...         ...         ...         ...         ...         ...   \n",
      "19995           9           3          10          10           3           5   \n",
      "19996           0           0           2           5          10          10   \n",
      "19997           0           1           0           4           3          10   \n",
      "19998           9          10           6           8           0           6   \n",
      "19999          10           4           0           6           5           1   \n",
      "\n",
      "       SZHBG-2039  SZHBG-2040  \n",
      "0               8           9  \n",
      "1               5           8  \n",
      "2               2           1  \n",
      "3               6           1  \n",
      "4               2           7  \n",
      "...           ...         ...  \n",
      "19995           4           9  \n",
      "19996           7           1  \n",
      "19997           9           0  \n",
      "19998           5           0  \n",
      "19999           7           4  \n",
      "\n",
      "[20000 rows x 23 columns]\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 20000 entries, 0 to 19999\n",
      "Data columns (total 23 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   序号          20000 non-null  int64 \n",
      " 1   层级          20000 non-null  int64 \n",
      " 2   零件名称        20000 non-null  object\n",
      " 3   零件编号        20000 non-null  object\n",
      " 4   供应商         20000 non-null  object\n",
      " 5   SZHBG-2023  20000 non-null  int64 \n",
      " 6   SZHBG-2024  20000 non-null  int64 \n",
      " 7   SZHBG-2025  20000 non-null  int64 \n",
      " 8   SZHBG-2026  20000 non-null  int64 \n",
      " 9   SZHBG-2027  20000 non-null  int64 \n",
      " 10  SZHBG-2028  20000 non-null  int64 \n",
      " 11  SZHBG-2029  20000 non-null  int64 \n",
      " 12  SZHBG-2030  20000 non-null  int64 \n",
      " 13  SZHBG-2031  20000 non-null  int64 \n",
      " 14  SZHBG-2032  20000 non-null  int64 \n",
      " 15  SZHBG-2033  20000 non-null  int64 \n",
      " 16  SZHBG-2034  20000 non-null  int64 \n",
      " 17  SZHBG-2035  20000 non-null  int64 \n",
      " 18  SZHBG-2036  20000 non-null  int64 \n",
      " 19  SZHBG-2037  20000 non-null  int64 \n",
      " 20  SZHBG-2038  20000 non-null  int64 \n",
      " 21  SZHBG-2039  20000 non-null  int64 \n",
      " 22  SZHBG-2040  20000 non-null  int64 \n",
      "dtypes: int64(20), object(3)\n",
      "memory usage: 3.5+ MB\n",
      "None\n",
      "价格清单\n",
      "          序号     零件名称         零件编号  零件价格      供应商\n",
      "0          1   零件5159  CLLMG-41836  2153   供应商685\n",
      "1          2  零件14754  QPVMN-58402  2881   供应商924\n",
      "2          3   零件9355  MNHXO-09645  1601  供应商1866\n",
      "3          4  零件14129  YGAXS-84375   832  供应商1475\n",
      "4          5  零件19173  SDSQO-10678  1193   供应商429\n",
      "...      ...      ...          ...   ...      ...\n",
      "19997  19996  零件19302  TNQJO-12436  2671   供应商805\n",
      "19998  19997  零件11381  ZTPTH-04829  2032  供应商1789\n",
      "19999  19998   零件1943  EKLHR-92617  1307  供应商1068\n",
      "20000  19999   零件9014  QWQGU-61073  1414   供应商391\n",
      "20001  20000   零件1177  FUECU-35894  2939   供应商334\n",
      "\n",
      "[20002 rows x 5 columns]\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 20002 entries, 0 to 20001\n",
      "Data columns (total 5 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   序号      20002 non-null  object\n",
      " 1   零件名称    20002 non-null  object\n",
      " 2   零件编号    20002 non-null  object\n",
      " 3   零件价格    20002 non-null  int64 \n",
      " 4   供应商     20002 non-null  object\n",
      "dtypes: int64(1), object(4)\n",
      "memory usage: 781.5+ KB\n",
      "None\n",
      "单车成本计算\n",
      "          车型代码  单车成本       参考答案\n",
      "0   SZHBG-2023   NaN  154005928\n",
      "1   SZHBG-2024   NaN  154598051\n",
      "2   SZHBG-2025   NaN  152218777\n",
      "3   SZHBG-2026   NaN  156421391\n",
      "4   SZHBG-2027   NaN  154406622\n",
      "5   SZHBG-2028   NaN  153516075\n",
      "6   SZHBG-2029   NaN  153903567\n",
      "7   SZHBG-2030   NaN  154438474\n",
      "8   SZHBG-2031   NaN  154205849\n",
      "9   SZHBG-2032   NaN  154125563\n",
      "10  SZHBG-2033   NaN  152970943\n",
      "11  SZHBG-2034   NaN  152867441\n",
      "12  SZHBG-2035   NaN  154709101\n",
      "13  SZHBG-2036   NaN  155267431\n",
      "14  SZHBG-2037   NaN  154019777\n",
      "15  SZHBG-2038   NaN  154406333\n",
      "16  SZHBG-2039   NaN  153707178\n",
      "17  SZHBG-2040   NaN  152594617\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 18 entries, 0 to 17\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   车型代码    18 non-null     object \n",
      " 1   单车成本    0 non-null      float64\n",
      " 2   参考答案    18 non-null     int64  \n",
      "dtypes: float64(1), int64(1), object(1)\n",
      "memory usage: 560.0+ bytes\n",
      "None\n",
      "Sheet1\n",
      "    Unnamed: 0  Unnamed: 1  Unnamed: 2  Unnamed: 3  Unnamed: 4  2023款白  \\\n",
      "0          NaN         NaN         NaN         NaN         1.1     NaN   \n",
      "1          NaN         NaN         NaN         NaN         1.2     NaN   \n",
      "2          NaN         NaN         NaN         NaN         1.3     NaN   \n",
      "3          NaN         NaN         NaN         NaN         1.4     NaN   \n",
      "4          NaN         NaN         NaN         NaN         1.5     NaN   \n",
      "5          NaN         NaN         NaN         NaN         1.6     NaN   \n",
      "6          NaN         NaN         NaN         NaN         1.7     NaN   \n",
      "7          NaN         NaN         NaN         NaN         1.8     NaN   \n",
      "8          NaN         NaN         NaN         NaN         1.9     NaN   \n",
      "9          NaN         NaN         NaN         NaN         2.0     NaN   \n",
      "10         NaN         NaN         NaN         NaN         2.1     NaN   \n",
      "11         NaN         NaN         NaN         NaN         2.2     NaN   \n",
      "12         NaN         NaN         NaN         NaN         2.3     NaN   \n",
      "13         NaN         NaN         NaN         NaN         2.4     NaN   \n",
      "14         NaN         NaN         NaN         NaN         2.5     NaN   \n",
      "15         NaN         NaN         NaN         NaN         2.6     NaN   \n",
      "16         NaN         NaN         NaN         NaN         2.7     NaN   \n",
      "17         NaN         NaN         NaN         NaN         2.8     NaN   \n",
      "18         NaN         NaN         NaN         NaN         2.9     NaN   \n",
      "19         NaN         NaN         NaN         NaN         3.0     NaN   \n",
      "20         NaN         NaN         NaN         NaN         3.1     NaN   \n",
      "21         NaN         NaN         NaN         NaN         3.2     NaN   \n",
      "\n",
      "    2024款白  2025款白  2026款白  2027款白  ...  2031款白  2032款白  2033款白  2034款白  \\\n",
      "0      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "1      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "2      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "3      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "4      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "5      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "6      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "7      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "8      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "9      NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "10     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "11     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "12     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "13     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "14     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "15     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "16     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "17     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "18     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "19     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "20     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "21     NaN     NaN     NaN     NaN  ...     NaN     NaN     NaN     NaN   \n",
      "\n",
      "    2035款白  2036款白  2037款白  2038款白  2039款白  2040款白  \n",
      "0      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "1      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "2      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "3      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "4      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "5      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "6      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "7      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "8      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "9      NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "10     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "11     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "12     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "13     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "14     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "15     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "16     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "17     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "18     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "19     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "20     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "21     NaN     NaN     NaN     NaN     NaN     NaN  \n",
      "\n",
      "[22 rows x 23 columns]\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 22 entries, 0 to 21\n",
      "Data columns (total 23 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   Unnamed: 0  0 non-null      float64\n",
      " 1   Unnamed: 1  0 non-null      float64\n",
      " 2   Unnamed: 2  0 non-null      float64\n",
      " 3   Unnamed: 3  0 non-null      float64\n",
      " 4   Unnamed: 4  22 non-null     float64\n",
      " 5   2023款白      0 non-null      float64\n",
      " 6   2024款白      0 non-null      float64\n",
      " 7   2025款白      0 non-null      float64\n",
      " 8   2026款白      0 non-null      float64\n",
      " 9   2027款白      0 non-null      float64\n",
      " 10  2028款白      0 non-null      float64\n",
      " 11  2029款白      0 non-null      float64\n",
      " 12  2030款白      0 non-null      float64\n",
      " 13  2031款白      0 non-null      float64\n",
      " 14  2032款白      0 non-null      float64\n",
      " 15  2033款白      0 non-null      float64\n",
      " 16  2034款白      0 non-null      float64\n",
      " 17  2035款白      0 non-null      float64\n",
      " 18  2036款白      0 non-null      float64\n",
      " 19  2037款白      0 non-null      float64\n",
      " 20  2038款白      0 non-null      float64\n",
      " 21  2039款白      0 non-null      float64\n",
      " 22  2040款白      0 non-null      float64\n",
      "dtypes: float64(23)\n",
      "memory usage: 4.1 KB\n",
      "None\n",
      "<class 'numpy.ndarray'>\n",
      "          0  1        2            3        4  5   6   7   8   9   ... 13  14  \\\n",
      "0          1  2      零件1  FOSFC-97058  供应商1994  8  10  10  10   4  ...  5   1   \n",
      "1          2  3      零件2  JYBWK-27081   供应商166  2   8   5   0   0  ...  2   0   \n",
      "2          3  1      零件3  IIVGK-51680   供应商174  0   5   0   7   4  ...  1   7   \n",
      "3          4  2      零件4  BCGZK-18967   供应商289  9   4   1   1   1  ...  2   1   \n",
      "4          5  3      零件5  EIZBA-09568  供应商1256  1   7   8   2  10  ...  9  10   \n",
      "...      ... ..      ...          ...      ... ..  ..  ..  ..  ..  ... ..  ..   \n",
      "19995  19996  2  零件19996  SARCL-96748   供应商673  6   3   4   9   6  ...  2   5   \n",
      "19996  19997  2  零件19997  MFZWY-69428   供应商926  4   2   3   3   7  ...  6   7   \n",
      "19997  19998  2  零件19998  PTACG-79360   供应商344  4  10   5  10   0  ...  4   7   \n",
      "19998  19999  1  零件19999  YLKTM-42175  供应商1841  7   7  10   5   2  ...  2   5   \n",
      "19999  20000  2  零件20000  DAYJQ-53618  供应商1673  3   3   0   9   3  ...  2   3   \n",
      "\n",
      "       15  16  17  18  19  20 21 22  \n",
      "0       4  10   9   5   8   4  8  9  \n",
      "1       0   9   8  10   8   5  5  8  \n",
      "2       6   8   9   6   5   3  2  1  \n",
      "3       5   6   8   7   9   2  6  1  \n",
      "4       9   7  10   5   9   9  2  7  \n",
      "...    ..  ..  ..  ..  ..  .. .. ..  \n",
      "19995   9   3  10  10   3   5  4  9  \n",
      "19996   0   0   2   5  10  10  7  1  \n",
      "19997   0   1   0   4   3  10  9  0  \n",
      "19998   9  10   6   8   0   6  5  0  \n",
      "19999  10   4   0   6   5   1  7  4  \n",
      "\n",
      "[20000 rows x 23 columns]\n",
      "<class 'str'>\n",
      "   序号  层级 零件名称         零件编号      供应商  SZHBG-2023  SZHBG-2024  SZHBG-2025  \\\n",
      "0   1   2  零件1  FOSFC-97058  供应商1994           8          10          10   \n",
      "\n",
      "   SZHBG-2026  SZHBG-2027  ...  SZHBG-2031  SZHBG-2032  SZHBG-2033  \\\n",
      "0          10           4  ...           5           1           4   \n",
      "\n",
      "   SZHBG-2034  SZHBG-2035  SZHBG-2036  SZHBG-2037  SZHBG-2038  SZHBG-2039  \\\n",
      "0          10           9           5           8           4           8   \n",
      "\n",
      "   SZHBG-2040  \n",
      "0           9  \n",
      "\n",
      "[1 rows x 23 columns]\n",
      "20000\n",
      "['序号_x' '层级' '零件名称_x' '零件编号' '供应商_x' 'SZHBG-2023' 'SZHBG-2024'\n",
      " 'SZHBG-2025' 'SZHBG-2026' 'SZHBG-2027' 'SZHBG-2028' 'SZHBG-2029'\n",
      " 'SZHBG-2030' 'SZHBG-2031' 'SZHBG-2032' 'SZHBG-2033' 'SZHBG-2034'\n",
      " 'SZHBG-2035' 'SZHBG-2036' 'SZHBG-2037' 'SZHBG-2038' 'SZHBG-2039'\n",
      " 'SZHBG-2040' '序号_y' '零件名称_y' '零件价格' '供应商_y' 'SZHBG-2023_multiply_零件价格'\n",
      " 'SZHBG-2024_multiply_零件价格' 'SZHBG-2025_multiply_零件价格'\n",
      " 'SZHBG-2026_multiply_零件价格' 'SZHBG-2027_multiply_零件价格'\n",
      " 'SZHBG-2028_multiply_零件价格' 'SZHBG-2029_multiply_零件价格'\n",
      " 'SZHBG-2030_multiply_零件价格' 'SZHBG-2031_multiply_零件价格'\n",
      " 'SZHBG-2032_multiply_零件价格' 'SZHBG-2033_multiply_零件价格'\n",
      " 'SZHBG-2034_multiply_零件价格' 'SZHBG-2035_multiply_零件价格'\n",
      " 'SZHBG-2036_multiply_零件价格' 'SZHBG-2037_multiply_零件价格'\n",
      " 'SZHBG-2038_multiply_零件价格' 'SZHBG-2039_multiply_零件价格'\n",
      " 'SZHBG-2040_multiply_零件价格']\n",
      "       SZHBG-2023_multiply_零件价格  SZHBG-2024_multiply_零件价格  \\\n",
      "0                         20384                     25480   \n",
      "1                          2420                      9680   \n",
      "2                             0                     12190   \n",
      "3                          2214                       984   \n",
      "4                          2405                     16835   \n",
      "...                         ...                       ...   \n",
      "19995                     11286                      5643   \n",
      "19996                      1244                       622   \n",
      "19997                      6604                     16510   \n",
      "19998                     13650                     13650   \n",
      "19999                      4503                      4503   \n",
      "\n",
      "       SZHBG-2025_multiply_零件价格  SZHBG-2026_multiply_零件价格  \\\n",
      "0                         25480                     25480   \n",
      "1                          6050                         0   \n",
      "2                             0                     17066   \n",
      "3                           246                       246   \n",
      "4                         19240                      4810   \n",
      "...                         ...                       ...   \n",
      "19995                      7524                     16929   \n",
      "19996                       933                       933   \n",
      "19997                      8255                     16510   \n",
      "19998                     19500                      9750   \n",
      "19999                         0                     13509   \n",
      "\n",
      "       SZHBG-2027_multiply_零件价格  SZHBG-2028_multiply_零件价格  \\\n",
      "0                         10192                     15288   \n",
      "1                             0                      9680   \n",
      "2                          9752                      9752   \n",
      "3                           246                      1230   \n",
      "4                         24050                     12025   \n",
      "...                         ...                       ...   \n",
      "19995                     11286                         0   \n",
      "19996                      2177                      1244   \n",
      "19997                         0                     13208   \n",
      "19998                      3900                      9750   \n",
      "19999                      4503                      7505   \n",
      "\n",
      "       SZHBG-2029_multiply_零件价格  SZHBG-2030_multiply_零件价格  \\\n",
      "0                          5096                      5096   \n",
      "1                          6050                      2420   \n",
      "2                             0                     12190   \n",
      "3                             0                      1230   \n",
      "4                         24050                     24050   \n",
      "...                         ...                       ...   \n",
      "19995                     18810                      1881   \n",
      "19996                       311                      2177   \n",
      "19997                      8255                      9906   \n",
      "19998                      7800                     11700   \n",
      "19999                     15010                     13509   \n",
      "\n",
      "       SZHBG-2031_multiply_零件价格  SZHBG-2032_multiply_零件价格  \\\n",
      "0                         12740                      2548   \n",
      "1                          2420                         0   \n",
      "2                          2438                     17066   \n",
      "3                           492                       246   \n",
      "4                         21645                     24050   \n",
      "...                         ...                       ...   \n",
      "19995                      3762                      9405   \n",
      "19996                      1866                      2177   \n",
      "19997                      6604                     11557   \n",
      "19998                      3900                      9750   \n",
      "19999                      3002                      4503   \n",
      "\n",
      "       SZHBG-2033_multiply_零件价格  SZHBG-2034_multiply_零件价格  \\\n",
      "0                         10192                     25480   \n",
      "1                             0                     10890   \n",
      "2                         14628                     19504   \n",
      "3                          1230                      1476   \n",
      "4                         21645                     16835   \n",
      "...                         ...                       ...   \n",
      "19995                     16929                      5643   \n",
      "19996                         0                         0   \n",
      "19997                         0                      1651   \n",
      "19998                     17550                     19500   \n",
      "19999                     15010                      6004   \n",
      "\n",
      "       SZHBG-2035_multiply_零件价格  SZHBG-2036_multiply_零件价格  \\\n",
      "0                         22932                     12740   \n",
      "1                          9680                     12100   \n",
      "2                         21942                     14628   \n",
      "3                          1968                      1722   \n",
      "4                         24050                     12025   \n",
      "...                         ...                       ...   \n",
      "19995                     18810                     18810   \n",
      "19996                       622                      1555   \n",
      "19997                         0                      6604   \n",
      "19998                     11700                     15600   \n",
      "19999                         0                      9006   \n",
      "\n",
      "       SZHBG-2037_multiply_零件价格  SZHBG-2038_multiply_零件价格  \\\n",
      "0                         20384                     10192   \n",
      "1                          9680                      6050   \n",
      "2                         12190                      7314   \n",
      "3                          2214                       492   \n",
      "4                         21645                     21645   \n",
      "...                         ...                       ...   \n",
      "19995                      5643                      9405   \n",
      "19996                      3110                      3110   \n",
      "19997                      4953                     16510   \n",
      "19998                         0                     11700   \n",
      "19999                      7505                      1501   \n",
      "\n",
      "       SZHBG-2039_multiply_零件价格  SZHBG-2040_multiply_零件价格  \n",
      "0                         20384                     22932  \n",
      "1                          6050                      9680  \n",
      "2                          4876                      2438  \n",
      "3                          1476                       246  \n",
      "4                          4810                     16835  \n",
      "...                         ...                       ...  \n",
      "19995                      7524                     16929  \n",
      "19996                      2177                       311  \n",
      "19997                     14859                         0  \n",
      "19998                      9750                         0  \n",
      "19999                     10507                      6004  \n",
      "\n",
      "[20000 rows x 18 columns]\n",
      "            SZHBG-2023_multiply_零件价格  SZHBG-2024_multiply_零件价格  \\\n",
      "col_result               154005928.0               154598051.0   \n",
      "\n",
      "            SZHBG-2025_multiply_零件价格  SZHBG-2026_multiply_零件价格  \\\n",
      "col_result               152218777.0               156421391.0   \n",
      "\n",
      "            SZHBG-2027_multiply_零件价格  SZHBG-2028_multiply_零件价格  \\\n",
      "col_result               154406622.0               153516075.0   \n",
      "\n",
      "            SZHBG-2029_multiply_零件价格  SZHBG-2030_multiply_零件价格  \\\n",
      "col_result               153903567.0               154438474.0   \n",
      "\n",
      "            SZHBG-2031_multiply_零件价格  SZHBG-2032_multiply_零件价格  \\\n",
      "col_result               154205849.0               154125563.0   \n",
      "\n",
      "            SZHBG-2033_multiply_零件价格  SZHBG-2034_multiply_零件价格  \\\n",
      "col_result               152970943.0               152867441.0   \n",
      "\n",
      "            SZHBG-2035_multiply_零件价格  SZHBG-2036_multiply_零件价格  \\\n",
      "col_result               154709101.0               155267431.0   \n",
      "\n",
      "            SZHBG-2037_multiply_零件价格  SZHBG-2038_multiply_零件价格  \\\n",
      "col_result               154019777.0               154406333.0   \n",
      "\n",
      "            SZHBG-2039_multiply_零件价格  SZHBG-2040_multiply_零件价格  \n",
      "col_result               153707178.0               152594617.0  \n",
      "             SZHBG-2023   SZHBG-2024   SZHBG-2025   SZHBG-2026   SZHBG-2027  \\\n",
      "col_result  154005928.0  154598051.0  152218777.0  156421391.0  154406622.0   \n",
      "\n",
      "             SZHBG-2028   SZHBG-2029   SZHBG-2030   SZHBG-2031   SZHBG-2032  \\\n",
      "col_result  153516075.0  153903567.0  154438474.0  154205849.0  154125563.0   \n",
      "\n",
      "             SZHBG-2033   SZHBG-2034   SZHBG-2035   SZHBG-2036   SZHBG-2037  \\\n",
      "col_result  152970943.0  152867441.0  154709101.0  155267431.0  154019777.0   \n",
      "\n",
      "             SZHBG-2038   SZHBG-2039   SZHBG-2040  \n",
      "col_result  154406333.0  153707178.0  152594617.0  \n",
      "12.533823490142822\n",
      "文件保存错误!错误信息为[Errno 13] Permission denied: 'C:/Users/31951/Desktop/请自定义名称/数据文件夹/技能大赛Python数据集.xlsx'\n",
      "False\n",
      "12.534822940826416\n"
     ]
    }
   ],
   "source": [
    "# 包导入 ----->>>>>>>>  开始\n",
    "import time\n",
    "\n",
    "from pythonProjectTemplate.Controller.file_controller import 文件操作\n",
    "from pythonProjectTemplate.Controller.pandas_query_controller import pandas数据筛查\n",
    "from pythonProjectTemplate.Controller.pandas_data_controller import 数据操作\n",
    "from pythonProjectTemplate.Controller.configurator_controller import 配置器\n",
    "from pythonProjectTemplate.Controller.numpy_calculation_controller import 矩阵运算\n",
    "from pythonProjectTemplate.Utils.print_logo_util import print_logo\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# 包导入 ----->>>>>>>>  结束\n",
    "\n",
    "# logo打印 ------>>>>>>>>>>  开始   不想要,可注释掉\n",
    "print_logo()\n",
    "# logo打印 ------>>>>>>>>>>  结束\n",
    "\n",
    "def main():\n",
    " \n",
    "\n",
    "    # 文件读取----->>>>>>>>  开始\n",
    "    start_time = time.time()\n",
    "    表单配置列表 = []\n",
    "    set_excel_properties_1 = (配置器().读取Excel表单配置()\n",
    "                              .配置表单名(\"BOM清单\")\n",
    "                              .跳过指定的行(1)\n",
    "                              .配置读取前几行(None)\n",
    "                              .配置读取的列(None))\n",
    "\n",
    "    set_excel_properties_2 = (配置器().读取Excel表单配置()\n",
    "                              .配置表单名(\"价格清单\")\n",
    "                              .跳过指定的行(None)\n",
    "                              .配置读取前几行(None)\n",
    "                              .配置读取的列(None))\n",
    "\n",
    "    set_excel_properties_3 = (配置器().读取Excel表单配置()\n",
    "                              .配置表单名(\"单车成本计算\")\n",
    "                              )\n",
    "    set_excel_properties_4 = (配置器().读取Excel表单配置()\n",
    "                              .配置表单名(\"Sheet1\")\n",
    "\n",
    "                              )\n",
    "\n",
    "    表单配置列表.append(set_excel_properties_1)\n",
    "    表单配置列表.append(set_excel_properties_2)\n",
    "    表单配置列表.append(set_excel_properties_3)\n",
    "    表单配置列表.append(set_excel_properties_4)\n",
    "\n",
    "    文件地址 = r\"C:\\Users\\31951\\Desktop\\请自定义名称\\数据文件夹\\技能大赛Python数据集.xlsx\"\n",
    "\n",
    "    file = 配置器().文件加载配置().配置Excel文件(文件地址, 表单配置列表)\n",
    "\n",
    "    源数据 = 文件操作().pandas加载Excel(file)\n",
    "    result = 矩阵运算().pandas与numpy矩阵数组转换(源数据.get(\"技能大赛Python数据集\").get(\"BOM清单\"))\n",
    "    print(type(result))\n",
    "    result = 矩阵运算().pandas与numpy矩阵数组转换(result)\n",
    "    print(result)\n",
    "\n",
    "    # 源数据 = 文件操作.openpyxl加载Excel(file)\n",
    "\n",
    "    # 文件读取----->>>>>>>>  结束\n",
    "\n",
    "    # todo 如果使用openpyxl读文件就使用这个代码段统一数据格式  -------->>>>>>  开始\n",
    "    # 指定列索引----------->>>>>>>>>>>>>>  开始\n",
    "    # 指定列索引配置文件 = 配置器().指定列索引配置().配置数据集(源数据.get(\"技能大赛Python数据集\").get(\"BOM清单\")).配置列索引行序号(1)\n",
    "    # result = 数据操作.设置列索引(指定列索引配置文件)\n",
    "    # 指定列索引----------->>>>>>>>>>>>>>  结束\n",
    "\n",
    "    # 删除指定行或列-------->>>>>>>>>>> 开始\n",
    "    # 删除行或列配置文件 = 配置器().删除行或列配置().配置数据集(result).配置行索引(0)\n",
    "    # result = 数据操作.指定行或列删除(删除行或列配置文件)\n",
    "    # print(result)\n",
    "    # 删除指定行或列-------->>>>>>>>>>> 结束\n",
    "    # todo 如果使用openpyxl读文件就使用这个代码段统一数据格式  -------->>>>>>  结束\n",
    "\n",
    "    # 获取指定列表内指定索引的值配置 ---->>>>>>>>>>>>>>  开始\n",
    "    数据截取配置文件 = (配置器().数据裁切配置()\n",
    "                        .配置文件名(\"技能大赛Python数据集\")\n",
    "                        .配置表单名(\"BOM清单\")\n",
    "                        .配置列索引([1, 2, 3]))\n",
    "    数据截取后文件 = pandas数据筛查().数据截取(源数据, 数据截取配置文件)\n",
    "    # 获取指定列表内指定索引的值配置 ---->>>>>>>>>>>>>>  结束\n",
    "\n",
    "    # 数据查重 ---->>>>>>>>>>>>>>  开始\n",
    "    数据查重配置文件 = (配置器().数据查重配置()\n",
    "                        .配置文件名(\"技能大赛Python数据集\")\n",
    "                        .配置表单名(\"价格清单\")\n",
    "                        .配置列索引([\"零件价格\"]))\n",
    "    pandas数据筛查().数据查重(源数据, [数据查重配置文件])\n",
    "    # 数据查重 ---->>>>>>>>>>>>>>  结束\n",
    "\n",
    "    # 删除指定重复数据------------->>>>>>>>>>>>>>>>>>>>>  开始\n",
    "    # print(源数据)\n",
    "    已筛除重复数据 = 数据操作().按照条件删除指定数据(源数据.get(\"技能大赛Python数据集\").get(\"价格清单\"),\n",
    "                                                     \"df['序号'] == 'test'\")\n",
    "\n",
    "    # 删除指定重复数据------------->>>>>>>>>>>>>>>>>>>>>  结束\n",
    "\n",
    "    # 表合并 --->>>>>>>>>>>>>>>>>  开始\n",
    "\n",
    "    表合并后数据 = pandas数据筛查().合并表([源数据.get(\"技能大赛Python数据集\").get(\"BOM清单\"),\n",
    "                                            已筛除重复数据],\n",
    "                                           [\"零件编号\"],\n",
    "                                           \"inner\")\n",
    "\n",
    "    # 表合并 --->>>>>>>>>>>>>>>>>  结束\n",
    "\n",
    "    # 筛查指定列是否包含某些值 --->>>>>>>>> 开始\n",
    "    result = pandas数据筛查().指定列值筛查(df=源数据.get(\"技能大赛Python数据集\").get(\"BOM清单\"), col_value=\"零件名称\",\n",
    "                                           value_list=[\"零件1\"])\n",
    "    print(result)\n",
    "\n",
    "    # 筛查指定列是否包含某些值 --->>>>>>>>> 结束\n",
    "\n",
    "    # todo 矩阵运算--------->>>>>>>这个方法很容易内存溢出,想办法限制传参 开始\n",
    "    矩阵运算配置 = (配置器().多列多行迭代运算配置().配置运算符(配置器().运算符().乘)\n",
    "                    .配置数据集1(源数据.get(\"技能大赛Python数据集\").get(\"BOM清单\"))\n",
    "                    .配置表1列索引(5)\n",
    "                    .配置表2列索引(3)\n",
    "                    .配置数据集2(源数据.get(\"技能大赛Python数据集\").get(\"价格清单\")))\n",
    "\n",
    "    result = 矩阵运算().多行与多列迭代运算(矩阵运算配置)\n",
    "    print(len(result))\n",
    "\n",
    "    # todo 矩阵运算--------->>>>>>>  结束\n",
    "\n",
    "    # 多列运算  --->>>>>>>  开始\n",
    "\n",
    "    多列运算配置 = (配置器().多列运算配置()\n",
    "                    .配置数据集(表合并后数据)\n",
    "                    .配置文件名(\"技能大赛Python数据集\")\n",
    "                    .配置表名(\"BOM清单\")\n",
    "                    #   [{\"key\":\"value\"},....]  SZHBG-2023_multiply_零件价格1\n",
    "                    .配置第一个参数([\"SZHBG-2023\",\n",
    "                                     \"SZHBG-2024\",\n",
    "                                     \"SZHBG-2025\",\n",
    "                                     \"SZHBG-2026\",\n",
    "                                     \"SZHBG-2027\",\n",
    "                                     \"SZHBG-2028\",\n",
    "                                     \"SZHBG-2029\",\n",
    "                                     \"SZHBG-2030\",\n",
    "                                     \"SZHBG-2031\",\n",
    "                                     \"SZHBG-2032\",\n",
    "                                     \"SZHBG-2033\",\n",
    "                                     \"SZHBG-2034\",\n",
    "                                     \"SZHBG-2035\",\n",
    "                                     \"SZHBG-2036\",\n",
    "                                     \"SZHBG-2037\",\n",
    "                                     \"SZHBG-2038\",\n",
    "                                     \"SZHBG-2039\",\n",
    "                                     \"SZHBG-2040\",\n",
    "                                     ])\n",
    "                    .配置第二个参数([\"零件价格\"])\n",
    "                    .配置运算符(配置器().运算符().乘))\n",
    "\n",
    "    result = 数据操作().多列运算(多列运算配置)\n",
    "    print(result.columns.values)\n",
    "\n",
    "    # 多列运算  --->>>>>>>  结束\n",
    "\n",
    "    sheet_properties = 配置器().数据裁切配置().配置列索引(\n",
    "        [27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44])\n",
    "\n",
    "    result = pandas数据筛查().数据截取(result, sheet_properties)\n",
    "    print(result)\n",
    "\n",
    "    result = 数据操作().指定行或列求和(result, 0, 18, \"\", flag=False)\n",
    "    数据截取配置文件 = (配置器().数据裁切配置()\n",
    "                        .配置行索引([20000]))\n",
    "    数据截取后文件 = pandas数据筛查().数据截取(result, 数据截取配置文件)\n",
    "    print(数据截取后文件)\n",
    "\n",
    "    # 更改列名(手动填充更好) ------------>>>>>>>>>>>  开始\n",
    "\n",
    "    列名配置文件 = (配置器().更改列名配置().配置新列名([\"SZHBG-2023\",\n",
    "                                                        \"SZHBG-2024\",\n",
    "                                                        \"SZHBG-2025\",\n",
    "                                                        \"SZHBG-2026\",\n",
    "                                                        \"SZHBG-2027\",\n",
    "                                                        \"SZHBG-2028\",\n",
    "                                                        \"SZHBG-2029\",\n",
    "                                                        \"SZHBG-2030\",\n",
    "                                                        \"SZHBG-2031\",\n",
    "                                                        \"SZHBG-2032\",\n",
    "                                                        \"SZHBG-2033\",\n",
    "                                                        \"SZHBG-2034\",\n",
    "                                                        \"SZHBG-2035\",\n",
    "                                                        \"SZHBG-2036\",\n",
    "                                                        \"SZHBG-2037\",\n",
    "                                                        \"SZHBG-2038\",\n",
    "                                                        \"SZHBG-2039\",\n",
    "                                                        \"SZHBG-2040\",\n",
    "                                                        ])\n",
    "                    .配置老列名(['SZHBG-2023_multiply_零件价格',\n",
    "                                 'SZHBG-2024_multiply_零件价格', 'SZHBG-2025_multiply_零件价格',\n",
    "                                 'SZHBG-2026_multiply_零件价格', 'SZHBG-2027_multiply_零件价格',\n",
    "                                 'SZHBG-2028_multiply_零件价格', 'SZHBG-2029_multiply_零件价格',\n",
    "                                 'SZHBG-2030_multiply_零件价格', 'SZHBG-2031_multiply_零件价格',\n",
    "                                 'SZHBG-2032_multiply_零件价格', 'SZHBG-2033_multiply_零件价格',\n",
    "                                 'SZHBG-2034_multiply_零件价格', 'SZHBG-2035_multiply_零件价格',\n",
    "                                 'SZHBG-2036_multiply_零件价格', 'SZHBG-2037_multiply_零件价格',\n",
    "                                 'SZHBG-2038_multiply_零件价格', 'SZHBG-2039_multiply_零件价格',\n",
    "                                 'SZHBG-2040_multiply_零件价格']))\n",
    "\n",
    "    result = 数据操作().修改列名(数据截取后文件, 列名配置文件)\n",
    "\n",
    "    # 更改列名(手动填充更好) ------------>>>>>>>>>>>  结束\n",
    "\n",
    "    print(result)\n",
    "    # 行列转置 ---------->>>>>>>  开始\n",
    "    行列转换后结果 = 数据操作().行列转换(result)\n",
    "    行列转换后结果 = 数据操作().重置索引列(行列转换后结果)\n",
    "    # 行列转置 ---------->>>>>>>  结束\n",
    "\n",
    "    # 修改列名 ----------->>>>>>>>>>>>  开始\n",
    "    列名配置文件 = 配置器().更改列名配置().配置老列名([\"index\", \"col_result\"]).配置新列名([\"车型代码\", \"单车成本\"])\n",
    "    result = 数据操作().修改列名(行列转换后结果, 列名配置文件)\n",
    "    # 修改列名 ----------->>>>>>>>>>>>  结束\n",
    "\n",
    "    # 表合并 -------->>>>>>>>>>> 开始\n",
    "    表合并后数据 = pandas数据筛查().合并表([源数据.get(\"技能大赛Python数据集\").get(\"单车成本计算\"),\n",
    "                                            result],\n",
    "                                           [\"车型代码\"],\n",
    "                                           \"inner\")\n",
    "    # 表合并 -------->>>>>>>>>>> 结束\n",
    "\n",
    "    # 列名顺序重构------------->>>>>>>>  开始\n",
    "    result = 数据操作().重构列排序(表合并后数据, [\"车型代码\", \"单车成本_y\", \"参考答案\"])\n",
    "    # 列名顺序重构------------->>>>>>>>  开始\n",
    "\n",
    "    # 修改列名 ----------->>>>>>>>>>>>  开始\n",
    "\n",
    "    列名配置文件 = 配置器().更改列名配置().配置老列名(\"单车成本_y\").配置新列名(\"单车成本\")\n",
    "    result = 数据操作().修改列名(result, 列名配置文件)\n",
    "    # 修改列名 ----------->>>>>>>>>>>>  结束\n",
    "\n",
    "    end_time = time.time()\n",
    "    print(end_time - start_time)\n",
    "\n",
    "    # 导出到Excel ---->>>>>>>>>>>>>   开始\n",
    "    save_file = r\"C:\\Users\\31951\\Desktop\\请自定义名称\\数据文件夹\\技能大赛Python数据集.xlsx\"\n",
    "\n",
    "    flag = 文件操作().导出数据到Excel(result, save_file, \"单车成本计算_结果1\", False)\n",
    "    print(flag)\n",
    "    # 导出到Excel ---->>>>>>>>>>>>>   开始\n",
    "    end_time = time.time()\n",
    "    print(end_time - start_time)\n",
    "\n",
    "\n",
    "# todo 共计15.657357215881348秒,数据分析用时5.472443580627441, 数据保存用时 10 秒,要优化\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    main()\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
